package com.bank.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.bank.utils.DbUtils;

public class RecordDAO {
	public static final Object[] columnNames = { "交易编号", "卡号", "交易时间", "交易类型", "交易金额", "余额", "对方姓名", "对方账户", "交易备注" };
	public static final List<Object[]> data = new ArrayList<>();

	public static Object[][] toList(List<Object[]> list) {
		Object[][] result = new Object[list.size()][];
		for (int i = 0; i < list.size(); i++) {
			result[i] = list.get(i);
		}
		return result;
	}

	//  查询转账记录信息
	//  col 要查询的列  ||  text 要查询的内容
	public static Object[][] search(int col, String text) {
		String sql = "select sn,card,created_time,type,amount,balance,other_name,other_account,remark from bank_record ";

		List<Object[]> result = new ArrayList<>();
		List param = new ArrayList<>();
		if (col == 1) {
			if (text != null && text.trim().length() > 0) {
				sql += " where card=?";
				param.add(text);
			}
		}
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			connection = DbUtils.open();
			ps = DbUtils.preparedStatement(sql, param, connection);
			rs = ps.executeQuery();
			while (rs.next()) {
				Object[] data = new Object[columnNames.length];
				data[0] = rs.getString("sn");
				data[1] = rs.getString("card");
				data[2] = rs.getString("created_time");
				data[3] = rs.getString("type");
				data[4] = rs.getString("amount");
				data[5] = rs.getString("balance");
				data[6] = rs.getString("other_name");
				data[7] = rs.getString("other_account");
				data[8] = rs.getString("remark");
				result.add(data);
			}
		} catch (Exception e) {
			e.printStackTrace();

		} finally {
			DbUtils.closeAll(connection, ps, null);
		}
		return toList(result);
	}

	/**
	 * 添加记录
	 */
	public static void add(Object[] obj) {
		String sql = "insert into bank_record (sn,card,created_time,type,amount,balance,other_name,other_account,remark) values(?,?,?,?,?,?,?,?,?)";
		List param = new ArrayList<>();
		for (Object o : obj) {
			param.add(o);
		}
		DbUtils.executeUpdate(sql, param);
	}

	/**
	 * 查询今天的累计交易金额
	 *            交易类型 in表示收入(存款，转入),out是支出(取款，转出)
	 */
	public static double getTodayAmount(String card, String type) {
		//  根据 type 的不同可以查询不同类型的交易
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		String today = format.format(new Date());
		double sum = 0; // 表示要交易的总金额

		String sql = "select sum(amount) from bank_record where card=? and DATE_FORMAT(created_time,'%Y-%m-%d')=? ";
		List param = new ArrayList<>();
		param.add(card);
		param.add(today);
		if ("in".equals(type)) {
			sql += " and amount>0";
		} else {
			sql += " and amount<0";
		}
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			connection = DbUtils.open();
			ps = DbUtils.preparedStatement(sql, param, connection);
			rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getDouble(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbUtils.closeAll(connection, ps, null);
		}
		return sum;
	}
}
